﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
using System.Configuration;
namespace Maticsoft.DAL
{
	/// <summary>
	/// 数据访问类:UserInfo
	/// </summary>
	public partial class UserInfo
	{
		public UserInfo()
		{}
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("user_id", "UserInfo");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int user_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where user_id=@user_id");
            SqlParameter[] parameters = {
					new SqlParameter("@user_id", SqlDbType.Int,4)
			};
            parameters[0].Value = user_id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Maticsoft.Model.UserInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into UserInfo(");
            strSql.Append("loginName,loginId,loginPass,remark,sys_CB,syc_FZ,sys_JY,sys_PX,sys_SB,sys_CB_uid,sys_FZ_uid,sys_JY_uid,sys_PX_uid,sys_SB_uid)");
            strSql.Append(" values (");
            strSql.Append("@loginName,@loginId,@loginPass,@remark,@sys_CB,@syc_FZ,@sys_JY,@sys_PX,@sys_SB,@sys_CB_uid,@sys_FZ_uid,@sys_JY_uid,@sys_PX_uid,@sys_SB_uid)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@loginName", SqlDbType.VarChar,50),
					new SqlParameter("@loginId", SqlDbType.VarChar,50),
					new SqlParameter("@loginPass", SqlDbType.VarChar,50),
					new SqlParameter("@remark", SqlDbType.VarChar,600),
					new SqlParameter("@sys_CB", SqlDbType.Bit,1),
					new SqlParameter("@syc_FZ", SqlDbType.Bit,1),
					new SqlParameter("@sys_JY", SqlDbType.Bit,1),
					new SqlParameter("@sys_PX", SqlDbType.Bit,1),
					new SqlParameter("@sys_SB", SqlDbType.Bit,1),
					new SqlParameter("@sys_CB_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_FZ_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_JY_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_PX_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_SB_uid", SqlDbType.Int,4)};
            parameters[0].Value = model.loginName;
            parameters[1].Value = model.loginId;
            parameters[2].Value = model.loginPass;
            parameters[3].Value = model.remark;
            parameters[4].Value = model.sys_CB;
            parameters[5].Value = model.syc_FZ;
            parameters[6].Value = model.sys_JY;
            parameters[7].Value = model.sys_PX;
            parameters[8].Value = model.sys_SB;
            parameters[9].Value = model.sys_CB_uid;
            parameters[10].Value = model.sys_FZ_uid;
            parameters[11].Value = model.sys_JY_uid;
            parameters[12].Value = model.sys_PX_uid;
            parameters[13].Value = model.sys_SB_uid;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Maticsoft.Model.UserInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set ");
            strSql.Append("loginName=@loginName,");
            strSql.Append("loginId=@loginId,");
            strSql.Append("loginPass=@loginPass,");
            strSql.Append("remark=@remark,");
            strSql.Append("sys_CB=@sys_CB,");
            strSql.Append("syc_FZ=@syc_FZ,");
            strSql.Append("sys_JY=@sys_JY,");
            strSql.Append("sys_PX=@sys_PX,");
            strSql.Append("sys_SB=@sys_SB,");
            strSql.Append("sys_CB_uid=@sys_CB_uid,");
            strSql.Append("sys_FZ_uid=@sys_FZ_uid,");
            strSql.Append("sys_JY_uid=@sys_JY_uid,");
            strSql.Append("sys_PX_uid=@sys_PX_uid,");
            strSql.Append("sys_SB_uid=@sys_SB_uid");
            strSql.Append(" where user_id=@user_id");
            SqlParameter[] parameters = {
					new SqlParameter("@loginName", SqlDbType.VarChar,50),
					new SqlParameter("@loginId", SqlDbType.VarChar,50),
					new SqlParameter("@loginPass", SqlDbType.VarChar,50),
					new SqlParameter("@remark", SqlDbType.VarChar,600),
					new SqlParameter("@sys_CB", SqlDbType.Bit,1),
					new SqlParameter("@syc_FZ", SqlDbType.Bit,1),
					new SqlParameter("@sys_JY", SqlDbType.Bit,1),
					new SqlParameter("@sys_PX", SqlDbType.Bit,1),
					new SqlParameter("@sys_SB", SqlDbType.Bit,1),
					new SqlParameter("@sys_CB_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_FZ_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_JY_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_PX_uid", SqlDbType.Int,4),
					new SqlParameter("@sys_SB_uid", SqlDbType.Int,4),
					new SqlParameter("@user_id", SqlDbType.Int,4)};
            parameters[0].Value = model.loginName;
            parameters[1].Value = model.loginId;
            parameters[2].Value = model.loginPass;
            parameters[3].Value = model.remark;
            parameters[4].Value = model.sys_CB;
            parameters[5].Value = model.syc_FZ;
            parameters[6].Value = model.sys_JY;
            parameters[7].Value = model.sys_PX;
            parameters[8].Value = model.sys_SB;
            parameters[9].Value = model.sys_CB_uid;
            parameters[10].Value = model.sys_FZ_uid;
            parameters[11].Value = model.sys_JY_uid;
            parameters[12].Value = model.sys_PX_uid;
            parameters[13].Value = model.sys_SB_uid;
            parameters[14].Value = model.user_id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int user_id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UserInfo ");
            strSql.Append(" where user_id=@user_id");
            SqlParameter[] parameters = {
					new SqlParameter("@user_id", SqlDbType.Int,4)
			};
            parameters[0].Value = user_id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string user_idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UserInfo ");
            strSql.Append(" where user_id in (" + user_idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Maticsoft.Model.UserInfo GetModel(int user_id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 user_id,loginName,loginId,loginPass,remark,sys_CB,syc_FZ,sys_JY,sys_PX,sys_SB,sys_CB_uid,sys_FZ_uid,sys_JY_uid,sys_PX_uid,sys_SB_uid from UserInfo ");
            strSql.Append(" where user_id=@user_id");
            SqlParameter[] parameters = {
					new SqlParameter("@user_id", SqlDbType.Int,4)
			};
            parameters[0].Value = user_id;

            Maticsoft.Model.UserInfo model = new Maticsoft.Model.UserInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Maticsoft.Model.UserInfo DataRowToModel(DataRow row)
        {
            Maticsoft.Model.UserInfo model = new Maticsoft.Model.UserInfo();
            if (row != null)
            {
                if (row["user_id"] != null && row["user_id"].ToString() != "")
                {
                    model.user_id = int.Parse(row["user_id"].ToString());
                }
                if (row["loginName"] != null)
                {
                    model.loginName = row["loginName"].ToString();
                }
                if (row["loginId"] != null)
                {
                    model.loginId = row["loginId"].ToString();
                }
                if (row["loginPass"] != null)
                {
                    model.loginPass = row["loginPass"].ToString();
                }
                if (row["remark"] != null)
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["sys_CB"] != null && row["sys_CB"].ToString() != "")
                {
                    if ((row["sys_CB"].ToString() == "1") || (row["sys_CB"].ToString().ToLower() == "true"))
                    {
                        model.sys_CB = true;
                    }
                    else
                    {
                        model.sys_CB = false;
                    }
                }
                if (row["syc_FZ"] != null && row["syc_FZ"].ToString() != "")
                {
                    if ((row["syc_FZ"].ToString() == "1") || (row["syc_FZ"].ToString().ToLower() == "true"))
                    {
                        model.syc_FZ = true;
                    }
                    else
                    {
                        model.syc_FZ = false;
                    }
                }
                if (row["sys_JY"] != null && row["sys_JY"].ToString() != "")
                {
                    if ((row["sys_JY"].ToString() == "1") || (row["sys_JY"].ToString().ToLower() == "true"))
                    {
                        model.sys_JY = true;
                    }
                    else
                    {
                        model.sys_JY = false;
                    }
                }
                if (row["sys_PX"] != null && row["sys_PX"].ToString() != "")
                {
                    if ((row["sys_PX"].ToString() == "1") || (row["sys_PX"].ToString().ToLower() == "true"))
                    {
                        model.sys_PX = true;
                    }
                    else
                    {
                        model.sys_PX = false;
                    }
                }
                if (row["sys_SB"] != null && row["sys_SB"].ToString() != "")
                {
                    if ((row["sys_SB"].ToString() == "1") || (row["sys_SB"].ToString().ToLower() == "true"))
                    {
                        model.sys_SB = true;
                    }
                    else
                    {
                        model.sys_SB = false;
                    }
                }
                if (row["sys_CB_uid"] != null && row["sys_CB_uid"].ToString() != "")
                {
                    model.sys_CB_uid = int.Parse(row["sys_CB_uid"].ToString());
                }
                if (row["sys_FZ_uid"] != null && row["sys_FZ_uid"].ToString() != "")
                {
                    model.sys_FZ_uid = int.Parse(row["sys_FZ_uid"].ToString());
                }
                if (row["sys_JY_uid"] != null && row["sys_JY_uid"].ToString() != "")
                {
                    model.sys_JY_uid = int.Parse(row["sys_JY_uid"].ToString());
                }
                if (row["sys_PX_uid"] != null && row["sys_PX_uid"].ToString() != "")
                {
                    model.sys_PX_uid = int.Parse(row["sys_PX_uid"].ToString());
                }
                if (row["sys_SB_uid"] != null && row["sys_SB_uid"].ToString() != "")
                {
                    model.sys_SB_uid = int.Parse(row["sys_SB_uid"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select user_id,loginName,loginId,loginPass,remark,sys_CB,syc_FZ,sys_JY,sys_PX,sys_SB,sys_CB_uid,sys_FZ_uid,sys_JY_uid,sys_PX_uid,sys_SB_uid ");
            strSql.Append(" FROM UserInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" user_id,loginName,loginId,loginPass,remark,sys_CB,syc_FZ,sys_JY,sys_PX,sys_SB,sys_CB_uid,sys_FZ_uid,sys_JY_uid,sys_PX_uid,sys_SB_uid ");
            strSql.Append(" FROM UserInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM UserInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.user_id desc");
            }
            strSql.Append(")AS Row, T.*  from UserInfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "UserInfo";
            parameters[1].Value = "user_id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
		#region  ExtensionMethod
        static string fzcon = ConfigurationManager.AppSettings["PreventionDB"];
        static string cbcon = ConfigurationManager.AppSettings["CeBaoDB"];
        static string sbcon = ConfigurationManager.AppSettings["dbstatus"];
        DbHelperSQLP fzDB = new DbHelperSQLP(fzcon);
        DbHelperSQLP cbDB = new DbHelperSQLP(cbcon);
        DbHelperSQLP sbDB = new DbHelperSQLP(sbcon);
        /// <summary>
        /// 获得防治数据列表
        /// </summary>
        public DataSet GetListFZUserInfo(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM UserInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return fzDB.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得测报数据列表
        /// </summary>
        public DataSet GetListCBUserInfo(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM UserInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return cbDB.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得设备监测数据列表
        /// </summary>
        public DataSet GetListSBUserInfo(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Sys_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return sbDB.Query(strSql.ToString());
        }

		#endregion  ExtensionMethod
	}
}

